PostgreSQL work_mem: What is work_mem? When to Consider Increasing work_mem?

PostgreSQL is renowned for its robust query optimization and memory management capabilities. One of the most critical yet often misunderstood parameters is work_mem. This memory setting can dramatically impact query performance, but it's not always clear when or how to tune it effectively.
we'll explore what work_mem is, how PostgreSQL uses it, and when you should consider increasing it to optimize your database performance.

What is work_mem?
work_mem is a PostgreSQL configuration parameter that defines the amount of memory allocated to each database operation that requires temporary storage. Unlike shared_buffers which is shared across all connections, work_mem is allocated per operation, per connection.

Key Characteristics:
- Per-operation allocation: Each sort, hash join, or merge operation gets its own work_mem
- Temporary memory: Used only during query execution
- Automatic cleanup: Memory is released when the operation completes
- Default value: 4MB (PostgreSQL 13+), 1MB in older versions

How PostgreSQL Uses work_mem:
PostgreSQL allocates work_mem for several types of operations:
1. Sort Operations
-- This query will use work_mem for sorting
SELECT * FROM large_table  ORDER BY created_at DESC LIMIT 1000;
2. Hash Joins
-- Hash joins use work_mem for building hash tables
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;
3. Merge Joins
-- Merge joins use work_mem for sorting inputs
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.ref_id;
4. Window Functions
-- Window functions may use work_mem for sorting
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;
5. Index Creation
-- Creating indexes uses work_mem for sorting
CREATE INDEX idx_created_at ON large_table(created_at);

Memory Allocation in Practice:
-- This query might use work_mem multiple times:
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.name
ORDER BY order_count DESC;
Memory allocation:
 1. Hash join: 1 × work_mem
 2. GROUP BY sorting: 1 × work_mem  
 3. ORDER BY sorting: 1 × work_mem
    Total: 3 × work_mem per connection

When to Consider Increasing work_mem:
1. Disk Spills (Temporary Files)
The most obvious sign that you need more work_mem is when PostgreSQL writes temporary files to disk:
-- Check for disk spills
SELECT query, temp_files, temp_bytes,  temp_bytes / temp_files as avg_temp_file_size FROM pg_stat_statements 
WHERE temp_files > 0 ORDER BY temp_bytes DESC;
What to look for:
   temp_files > 0: Indicates operations spilled to disk
   High temp_bytes: Shows significant disk usage
   Frequent spills: Operations consistently exceeding memory

2. Slow Query Performance 
Monitor query execution plans for external sorts:
-- Check if sorts are using external files
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM large_table  ORDER BY created_at DESC;
Look for:
- External Sort in the execution plan
- High I/O time in EXPLAIN ANALYZE
- temp_files in the plan output

3. Large Data Processing
Consider increasing work_mem when:
- Processing large datasets (>1GB)
- Running complex analytical queries
- Creating indexes on large tables
- Performing bulk data operations

4. Memory-Intensive Operations
Operations that typically benefit from more work_mem:
- Large sorts: ORDER BY on millions of rows
- Hash joins: Joining large tables
- Window functions: Complex analytical queries
- Aggregations: GROUP BY on large datasets

How to Configure work_mem
1. Check Current Setting
-- View current work_mem setting
SHOW work_mem;
-- View all memory-related settings
SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE '%mem%';

2. Set work_mem for Session
-- Temporary setting for current session
SET work_mem = '256MB';
-- Verify the change
SHOW work_mem;

3. Set work_mem Globally
-- Set globally (requires superuser privileges)
ALTER SYSTEM SET work_mem = '256MB';

-- Reload configuration
SELECT pg_reload_conf();
-- Restart may be required for some changes

4. Set work_mem in postgresql.conf
# In postgresql.conf
work_mem = 256MB
Restart PostgreSQL after changes

Memory Calculation and Planning
Total Memory Usage
Total work_mem = work_mem × max_connections × concurrent_operations
Example calculation:
- work_mem = 256MB
- max_connections = 100
- Average concurrent operations per connection = 2
- Total work_mem = 256MB × 100 × 2 = 51.2GB

Check your memory configuration:
SELECT  name,setting,unit, CASE 
 WHEN name = 'shared_buffers' THEN 'Shared across all connections'
        WHEN name = 'work_mem' THEN 'Per operation, per connection'
        WHEN name = 'maintenance_work_mem' THEN 'Per maintenance operation'
        END as description
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');

Common Pitfalls to Avoid:
1. Setting Too High
- Can cause memory pressure
- May lead to OOM (Out of Memory) errors
- Reduces available memory for other operations
2. Ignoring Concurrent Operations
- Multiple operations per connection multiply memory usage
- Consider peak concurrent load, not just max_connections
3. Not Monitoring Long-term
- Performance can change as data grows
- Regular monitoring is essential
4. One-Size-Fits-All Approach
- Different workloads may need different settings
- Consider per-query tuning for critical operations

Monitoring Queries:
-- Monitor work_mem usage
SELECT  pid,usename,application_name,state,query_start,query,temp_files,temp_bytes
FROM pg_stat_activity WHERE temp_files > 0;

-- Check for memory pressure
SELECT name,setting,unit FROM pg_settings WHERE name IN ('work_mem', 'shared_buffers', 'effective_cache_size');